"""
预算相关的数据库操作函数
"""

from .db import get_db_connection, close_connection

async def get_budgets(user_id):
    """
    获取用户的预算列表
    
    Args:
        user_id: 用户ID
    
    Returns:
        list: 预算列表，包含每个预算的详细信息
    """
    connection = None
    try:
        connection = await get_db_connection()
        async with connection.cursor() as cursor:
            sql = """
                SELECT 
                    b.id,
                    b.user_id,
                    b.category,
                    b.amount,
                    b.description,
                    b.created_date,
                    b.sort_order,
                    COALESCE(SUM(CASE WHEN bl.type = 0 THEN bl.amount ELSE 0 END), 0) as actual_expense,
                    b.amount - COALESCE(SUM(CASE WHEN bl.type = 0 THEN bl.amount ELSE 0 END), 0) as remaining_amount,
                    DATE_FORMAT(b.created_date, '%%Y-%%m') as budget_month
                FROM budgets b
                LEFT JOIN bills bl ON b.user_id = bl.user_id 
                    AND b.category = bl.bill_type 
                    AND DATE_FORMAT(bl.due_date, '%%Y-%%m') = DATE_FORMAT(CURRENT_DATE, '%%Y-%%m')
                WHERE b.user_id = %s
                GROUP BY b.id, b.user_id, b.category, b.amount, b.description, b.created_date, b.sort_order
                ORDER BY b.sort_order ASC, b.created_date DESC
            """
            await cursor.execute(sql, (user_id,))
            budgets = await cursor.fetchall()
            return [dict(budget) for budget in budgets]
    except Exception as e:
        print(f"获取预算列表失败: {str(e)}")
        return None
    finally:
        await close_connection(connection)

async def add_budget(user_id, category, amount, description):
    """
    添加新预算
    
    Args:
        user_id: 用户ID
        category: 预算类别
        amount: 预算金额
        description: 预算描述
    
    Returns:
        int: 新预算的ID，失败返回None
    """
    connection = None
    try:
        connection = await get_db_connection()
        async with connection.cursor() as cursor:
            sql = """
                INSERT INTO budgets (user_id, category, amount, description)
                VALUES (%s, %s, %s, %s)
            """
            await cursor.execute(sql, (user_id, category, amount, description))
            await connection.commit()
            return cursor.lastrowid
    except Exception as e:
        print(f"添加预算失败: {str(e)}")
        return None
    finally:
        await close_connection(connection)

async def update_budget(user_id, budget_id, category, amount, description):
    """
    更新预算信息
    
    Args:
        user_id: 用户ID
        budget_id: 预算ID
        category: 预算类别
        amount: 预算金额
        description: 预算描述
    
    Returns:
        bool: 是否更新成功
    """
    connection = None
    try:
        connection = await get_db_connection()
        async with connection.cursor() as cursor:
            sql = """
                UPDATE budgets 
                SET category = %s, amount = %s, description = %s
                WHERE user_id = %s AND id = %s
            """
            await cursor.execute(sql, (category, amount, description, user_id, budget_id))
            await connection.commit()
            return cursor.rowcount > 0
    except Exception as e:
        print(f"更新预算失败: {str(e)}")
        return False
    finally:
        await close_connection(connection)

async def delete_budget(user_id, budget_id):
    """
    删除预算
    
    Args:
        user_id: 用户ID
        budget_id: 预算ID
    
    Returns:
        bool: 是否删除成功
    """
    connection = None
    try:
        connection = await get_db_connection()
        async with connection.cursor() as cursor:
            sql = """
                DELETE FROM budgets 
                WHERE user_id = %s AND id = %s
            """
            await cursor.execute(sql, (user_id, budget_id))
            await connection.commit()
            return cursor.rowcount > 0
    except Exception as e:
        print(f"删除预算失败: {str(e)}")
        return False
    finally:
        await close_connection(connection)

async def update_budget_order(user_id, budget_orders):
    """
    更新预算排序
    
    Args:
        user_id: 用户ID
        budget_orders: 包含预算ID和排序值的列表 [{"id": 1, "sort_order": 0}, ...]
    
    Returns:
        bool: 是否更新成功
    """
    connection = None
    try:
        connection = await get_db_connection()
        async with connection.cursor() as cursor:
            for order in budget_orders:
                sql = """
                    UPDATE budgets 
                    SET sort_order = %s
                    WHERE user_id = %s AND id = %s
                """
                await cursor.execute(sql, (order["sort_order"], user_id, order["id"]))
            await connection.commit()
            return True
    except Exception as e:
        print(f"更新预算排序失败: {str(e)}")
        return False
    finally:
        await close_connection(connection) 